In [1]:
from ris import db2 #library designed for SQL database connection and querying
from IPython.display import clear_output
import datetime
from datetime import date
import pandas as pd
import os
clear_output()
timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
print 'Notebook run: {}'.format(timestamp)
print os.getcwd()
# %load_ext sql
In [2]:
#Database connections
#gdb = db2.PostgresDb('dotdevpgsql02', 'GISGRID', quiet = True)
cdb = db2.PostgresDb('DOTDEVRHPGSQL01', 'CRASHDATA', quiet = True)
sdb = db2.PostgresDb('dotpgsql01', 'sip', quiet = True)
#DONE FROM SIP Database
Function to the most recent full month at the time of running¶
In [7]:
#date.today(today.year, today.month, 1)
#date2 = date(today.year, today.month, 1) - datetime.timedelta(1)
#date2
def mrf_month():
today = date.today()
try:
tm = datetime.datetime(today.year, today.month, today.day+1)
lim= date(today.year, today.month, 1) - datetime.timedelta(1)
except ValueError:
lim= today
return(str(lim))
mrf_month()
Out[7]:
In [8]:
sip_segs = (db2.query_to_table(sdb, """
SELECT distinct* FROM(
SELECT sp.pid, sp.pjct_name, sp.pm, sp.sip_year, sp.start_date, sp.end_date, spl.description, spg.segmentid, st_setsrid(spg.geom,2263) geom
FROM public.sip_projects sp
join public.sip_projects_geo spg
on sp.pid=spg.pid_fk
join public.sip_lookup spl
on sp.vz_status::varchar = spl.lookupid::varchar
join public.sip_lookup spl2
on sp.unit::varchar = spl2.lookupid::varchar
where sp.status= '15'
and spl.description != 'None'
and spg.nodeid=0
and sp.end_date<='{}'::date
order by sp.end_date) sip_segs""".format(mrf_month()))
)
#print(sip_segs[0])
#print(sip_segs[1])
#sip_segs[0].dtypes
#select inet_server_addr() ip
#select inet_server_port() port
This table only contains projects that are nodes¶
In [10]:
sip_nodes = (db2.query_to_table(sdb, """
SELECT distinct* FROM(
SELECT sp.pid, sp.pjct_name, sp.pm, sp.sip_year, sp.start_date, sp.end_date, spl.description, spg.nodeid, st_setsrid(spg.geom,2263) geom
FROM public.sip_projects sp
join public.sip_projects_geo spg
on sp.pid=spg.pid_fk
join public.sip_lookup spl
on sp.vz_status::varchar = spl.lookupid::varchar
join public.sip_lookup spl2
on sp.unit::varchar = spl2.lookupid::varchar
where sp.status= '15'
and spl.description != 'None'
and spg.segmentid=0
and sp.end_date<='{}'::date
order by sp.end_date) sip_nodes""".format(mrf_month()))
)
#print(sip_nodes)
Function for Writing dataframes to pgsql using sqlalchemy¶
In [14]:
from sqlalchemy import create_engine
def df_to_sql(df,tbl_name,db):
engine = create_engine('postgresql://{user}:{pw}@10.243.154.88:5432/CRASHDATA'.format(user=db.params['user'],
pw=db.params['password']),
echo=False)
df.to_sql(name='{}'.format(tbl_name), con= engine, if_exists = 'replace', index=False)
db.query("""ALTER TABLE {tbl}
ALTER COLUMN geom TYPE Geometry USING geom::Geometry;
grant all on {tbl} to public;""".format(tbl=tbl_name))
return tbl_name
Writing dataframes to tables in pgsql¶
In [15]:
segs = df_to_sql(sip_segs,'sip_segs',cdb)
nodes = df_to_sql(sip_nodes,'sip_nodes',cdb)
Exporting pgtables to shape files¶
In [177]:
db2.pg_shp.export_pg_table_to_shp(r'{path}\sip_shps\segs'.format(path=os.getcwd()),
db,segs, shp_name=segs + '_{mrf}'.format(mrf=datetime.datetime.strptime(mrf_month(), '%Y-%m-%d').strftime("%m%d%Y")))
db2.pg_shp.export_pg_table_to_shp(r'{path}\sip_shps\nodes'.format(path=os.getcwd()),
db,nodes, shp_name=nodes + '_{mrf}'.format(mrf=datetime.datetime.strptime(mrf_month(), '%Y-%m-%d').strftime("%m%d%Y")))
In [16]:
from IPython.display import Image
PATH = "C:\Users\soge\Desktop\Jupyter\Requests\AG\SIP_to_VZV"
Image(filename = PATH + "\SIP_to_VZV701.png", width=1000, height=1000)
Out[16]:
In [15]:
cdb.query( """DROP TABLE if exists {segs};
DROP TABLE if exists {nodes};""".format(segs=segs, nodes=nodes))
Out[15]:
In [5]:
from ris import pg_import_export_shps as shp
shp.import_shp_to_pg('E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV\VZV_shps\SIP_Corridors.shp', cdb, schema='public', precision=False,
permission=True, gdal_data=r"C:\Program Files (x86)\GDAL\gdal-data")
In [7]:
shp.import_shp_to_pg('E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV\VZV_shps\SIP_Intersections.shp', cdb, schema='public', precision=False,
permission=True, gdal_data=r"C:\Program Files (x86)\GDAL\gdal-data")
In [17]:
sip_corr = (db2.query_to_table(ddb, """
SELECT *
FROM public.sip_projects sp
join public.sip_projects_geo spg
on sp.pid=spg.pid_fk
where sip_year > 2008 and sip_year<2014
and spg.nodeid=0
order by sip_year;""")
)
sip_itx = (db2.query_to_table(ddb, """
SELECT *
FROM public.sip_projects sp
join public.sip_projects_geo spg
on sp.pid=spg.pid_fk
where sip_year > 2008 and sip_year<2014
and spg.segmentid=0
order by sip_year;""")
)
In [18]:
corr = df_to_sql(sip_corr,'sip_corr',cdb)
itx = df_to_sql(sip_itx,'sip_itx',cdb)
In [34]:
sip_itx_ints = (db2.query_to_table(cdb,"""
select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
"SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm,
sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ
from working.sip_corridors vzv
join sip_corr sip
on st_dwithin(st_setsrid(vzv."geometry",2263), st_setsrid(sip.geom,2263),65)
where "SIP_YR" between 2009 and 2013"""))"""))
In [28]:
sip_itx_ints.to_csv("Sip_to_VZV_Itx_Overlap-{}.csv".format(datetime.datetime.now().strftime('%Y-%m-%d')),index=False)
MAP OF INTERSECTION OVERLAPS¶
In [82]:
from IPython.display import Image
PATH = "E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV"
Image(filename = PATH + "\Sip_to_vzv_itx_overlap-812.png", width=1000, height=1000)
Out[82]:
In [ ]:
sip_corr_ints = (db2.query_to_table(cdb,"""
select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
"SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm,
sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ
from working.sip_corridors vzv
join sip_corr sip
on st_dwithin(st_setsrid(vzv."geometry",2263), st_setsrid(sip.geom,2263),65)
where "SIP_YR" between 2009 and 2013"""))
In [ ]:
sip_corr_ints.to_csv("Sip_to_VZV_Corr_Overlap-{}.csv".format(datetime.datetime.now().strftime('%Y-%m-%d')),index=False)
MAP OF CORRIDOR OVERLAPS¶
In [51]:
from IPython.display import Image
PATH = "E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV"
Image(filename = PATH + "\Sip_to_zzv_corr_overlap-812.png", width=1000, height=1000)
Out[51]:
QUERY FOR COMPLETE OVERLAP¶
In [92]:
CO = (db2.query_to_table(cdb,"""
--COMPLETE OVERLAP
select distinct sip_pid, sip_pjct_name, sip_year, sip_pm, vzv_sip_id, vzv_pjct_name, vzv_sip_year, vzv_pm, typ
from (
select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
"SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm,
sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ
from working.sip_corridors vzv
join sip_corr sip
on st_dwithin(st_setsrid(vzv."geometry",2263), st_setsrid(sip.geom,2263),300)
where "SIP_YR" between 2009 and 2013
union all
select sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm,
sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ
from sip_intersections vzv
join sip_itx sip
on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
where vzv.sip_yr between 2009 and 2013
)corr_itx"""))
In [94]:
CO.to_csv('SIP_VZV_matches-812.csv')
MAP OF COMPLETE OVERLAP¶
In [83]:
from IPython.display import Image
PATH = "E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV"
Image(filename = PATH + "\complete_overlap-812.png", width=1000, height=1000)
Out[83]:
In [18]:
sip_vzv_overlap[['sip_pjct_name','vzv_pjct_name','sip_year','vzv_sip_year','typ']].to_csv('sip_vzv_pjctss.csv')
SIP IDS that don't HAVE a VZV match¶
In [84]:
sip = (db2.query_to_table(cdb,"""
select * from (
--Sip CORR PIDS that don't have a VZV match
select distinct x.sip_pid, x.sip_pjct_name, x.sip_year, x.sip_pm, x.typ
from (select distinct pid sip_pid, pjct_name sip_pjct_name, sip_year sip_year, pm sip_pm, segmentid sip_geomid, 'CORR' typ from sip_corr) x
left join (select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
"SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm,
sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ
from working.sip_corridors vzv
join sip_corr sip
on st_dwithin(st_setsrid(vzv."geometry",2263), st_setsrid(sip.geom,2263),65)
where "SIP_YR" between 2009 and 2013) y
on x.sip_geomid = y.sip_geomid
where vzv_sip_id is null
order by x.sip_pjct_name
) corrs
union
select * from (
--Sip ITX PIDS that don't have a VZV match
select x.sip_pid, x.sip_pjct_name, x.sip_year, x.sip_pm, x.typ
from (select distinct pid sip_pid, pjct_name sip_pjct_name, sip_year sip_year, pm sip_pm, nodeid sip_geomid, 'ITX' typ from sip_itx) x
left join (select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm,
sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ
from sip_intersections vzv
join sip_itx sip
on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
where vzv.sip_yr between 2009 and 2013) y
on x.sip_geomid = y.sip_geomid
where vzv_sip_id is null
order by x.sip_pjct_name
) itxs
"""))
In [91]:
sip.to_csv('SIP_unmatched-812.csv')
VZV PIDS that don't have a SIP match¶
In [87]:
vzv= (db2.query_to_table(cdb,"""
select * from (
--VZV CORR PIDS that don't have a SIP match
select distinct x.vzv_sip_id, x.vzv_pjct_name, x.vzv_sip_year, x.vzv_pm, x.typ
from (select distinct "SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm, 'CORR' typ, st_setsrid("geometry",2263) vzv_geom
from working.sip_corridors where "SIP_YR" between 2009 and 2013) x
left join (select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
"SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm,
sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ, st_setsrid(vzv."geometry",2263) vzv_geom
from working.sip_corridors vzv
join sip_corr sip
on st_dwithin(st_setsrid(vzv."geometry",2263), st_setsrid(sip.geom,2263),65)
where "SIP_YR" between 2009 and 2013) y
on x.vzv_geom = y.vzv_geom
where y.sip_pid is null
order by x.vzv_sip_id
) corrs
union
select * from (
--VZV ITX PIDS that don't have a SIP match
select distinct x.vzv_sip_id, x.vzv_pjct_name, x.vzv_sip_year, x.vzv_pm, x.typ
from (select distinct sip_id vzv_sip_id, pjct_name vzv_pjct_name, sip_yr vzv_sip_year, pm vzv_pm, 'ITX' typ, nodeid_1 geom
from sip_intersections where sip_yr between 2009 and 2013) x
left join (select sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm,
sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ
from sip_intersections vzv
join sip_itx sip
on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
where vzv.sip_yr between 2009 and 2013) y
on x.geom = y.vzv_geomid
where y.sip_pid is null
order by x.vzv_sip_id
) itxs """))
In [90]:
vzv.to_csv('VZV_unmatched-812.csv')